package top.chengdongqing.mishop.repositories

import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query
import top.chengdongqing.mishop.entities.Product
import top.chengdongqing.mishop.product.dto.ProductSearchDTO
import top.chengdongqing.mishop.product.projection.RecommendedProduct

interface ProductRepository : JpaRepository<Product, Int> {

    fun findByIdAndEnabledTrue(id: Int): Product?

    @Query(
        """
        FROM Product p 
        WHERE p.category.id = ?1 
            AND p.enabled = true 
        ORDER BY p.sortNo, p.createdAt DESC
    """
    )
    fun findAllByCategoryId(categoryId: Int, pageable: Pageable): List<Product>

    @Query(
        """
        FROM Product p
        WHERE p.brand.id = ?1
            AND p.enabled = true
        ORDER BY p.sortNo, p.createdAt DESC
    """
    )
    fun findAllByBrandId(brandId: Int, pageable: Pageable): List<Product>

    @Query(
        """
        SELECT p.name
        FROM Product p
        WHERE (:keyword IS NULL OR p.name LIKE %:keyword%)
            AND p.enabled = true
    """
    )
    fun findNamesByNameLike(keyword: String?, pageable: Pageable): List<String>

    @Query(
        """
        FROM
            Product p
        LEFT JOIN ProductSKU ps ON p.id = ps.product.id
        WHERE
            p.enabled = true
            AND (:#{#dto.categoryId} IS NULL OR p.category.id = :#{#dto.categoryId})
            AND (:#{#dto.brandId} IS NULL OR p.brand.id = :#{#dto.brandId})
            AND (:#{#dto.keyword} IS NULL OR p.name LIKE %:#{#dto.keyword}%)
            AND (
              :#{#dto.onlyAvailable} IS NULL OR
              :#{#dto.onlyAvailable} = FALSE OR
              ps.stocks > 0
            )
        GROUP BY p.id
        ORDER BY
            CASE WHEN :#{#dto.sortBy?.name()} = 'NEWEST' THEN p.createdAt END DESC,
            CASE WHEN :#{#dto.sortBy?.name()} = 'SALES' THEN p.sales END DESC,
            CASE WHEN :#{#dto.sortBy?.name()} = 'PRICE_ASC' THEN p.price END ASC,
            CASE WHEN :#{#dto.sortBy?.name()} = 'PRICE_DESC' THEN p.price END DESC
    """
    )
    fun findAllByPage(dto: ProductSearchDTO, pageable: Pageable): Page<Product>

    @Query(
        """
        SELECT
            ANY_VALUE(p.id) AS productId,
            ANY_VALUE(p.name) AS productName,
            ps.pictureUrl AS pictureUrl,
            ANY_VALUE(ps.id) AS skuId,
            ANY_VALUE(ps.name) AS skuName,
            ANY_VALUE(ps.price) AS price,
            ANY_VALUE(ps.originalPrice) AS originalPrice,
            ANY_VALUE(ps.limits) AS limits,
            (
                SELECT COUNT(*)
                FROM ProductReview
                WHERE productId = ANY_VALUE(p.id) AND rating >= 3
            ) AS reviews
        FROM
            Product p
        INNER JOIN ProductSKU ps ON p.id = ps.product.id
        WHERE
            p.enabled = true AND ps.stocks > 0
        GROUP BY
            ps.pictureUrl
        ORDER BY
            RAND()
    """
    )
    fun findRecommended(pageable: Pageable): List<RecommendedProduct>
}